<?php
// students_excel_import.php
require_once '../includes/dbcon.php';

// Check if user is logged in
if (!is_logged_in()) {
    redirect('login.php');
}

// Initialize variables
$message = '';
$message_type = '';
$import_stats = ['success' => 0, 'errors' => 0, 'error_details' => []];

// Get classes and academic years for dropdowns
function getClasses() {
    global $DBcon;
    try {
        $sql = "SELECT classid FROM class ORDER BY classid";
        $stmt = $DBcon->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        error_log("Get classes error: " . $e->getMessage());
        return [];
    }
}

function getAcademicYears() {
    global $DBcon;
    try {
        $sql = "SELECT academic_year FROM calender ORDER BY academic_year DESC";
        $stmt = $DBcon->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        error_log("Get academic years error: " . $e->getMessage());
        return [];
    }
}

$classes = getClasses();
$academic_years = getAcademicYears();

// Handle file upload and import
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['import'])) {
    // Validate CSRF token
    if (!validate_csrf_token($_POST['csrf_token'])) {
        $message = 'Security token validation failed.';
        $message_type = 'error';
    } else {
        // Validate required fields
        $class_id = sanitize_input($_POST['class_id']);
        $admin_year = sanitize_input($_POST['admin_year']);
        
        if (empty($class_id) || empty($admin_year)) {
            $message = 'Please select both class and academic year.';
            $message_type = 'error';
        } elseif (!isset($_FILES['excel_file']) || $_FILES['excel_file']['error'] !== UPLOAD_ERR_OK) {
            $message = 'Please select a valid Excel file to upload.';
            $message_type = 'error';
        } else {
            // Process the Excel file
            $result = processExcelUpload($_FILES['excel_file'], $class_id, $admin_year);
            if ($result['success']) {
                $message = "Import completed successfully! {$result['stats']['success']} students imported, {$result['stats']['errors']} errors.";
                $message_type = 'success';
                $import_stats = $result['stats'];
            } else {
                $message = $result['message'];
                $message_type = 'error';
                if (isset($result['stats'])) {
                    $import_stats = $result['stats'];
                }
            }
        }
    }
}

function processExcelUpload($file, $class_id, $admin_year) {
    global $DBcon;
    
    $allowed_types = [
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        'application/vnd.ms-excel'
    ];
    
    // Validate file type
    if (!in_array($file['type'], $allowed_types)) {
        return [
            'success' => false,
            'message' => 'Invalid file type. Please upload an Excel file (.xlsx).'
        ];
    }
    
    try {
        $students_data = parseXLSXFile($file['tmp_name']);
        
        if (empty($students_data)) {
            return [
                'success' => false,
                'message' => 'No valid student data found in the file or unable to parse the file.'
            ];
        }
        
        $stats = ['success' => 0, 'errors' => 0, 'error_details' => []];
        $row_count = 0;
        
        // Start transaction for atomic operations
        $DBcon->beginTransaction();
        
        foreach ($students_data as $row_index => $row) {
            $row_count++;
            
            // Skip header row (assuming first row is header)
            if ($row_count === 1) continue;
            
            // Skip empty rows
            if (empty($row['fullname']) && empty($row['regno'])) continue;
            
            $fullname = sanitize_input($row['fullname']);
            $regno = sanitize_input($row['regno']);
            
            // Validate required fields
            if (empty($fullname) || empty($regno)) {
                $stats['errors']++;
                $stats['error_details'][] = "Row $row_count: Missing name or registration number";
                continue;
            }
            
            // Check if registration number already exists
            if (registrationNumberExists($regno)) {
                $stats['errors']++;
                $stats['error_details'][] = "Row $row_count: Registration number '$regno' already exists";
                continue;
            }
            
            // Insert student
            if (insertStudent($fullname, $regno, $class_id, $admin_year)) {
                $stats['success']++;
            } else {
                $stats['errors']++;
                $stats['error_details'][] = "Row $row_count: Failed to insert student '$fullname'";
            }
        }
        
        // Commit transaction
        $DBcon->commit();
        
        return [
            'success' => true,
            'stats' => $stats
        ];
        
    } catch (Exception $e) {
        // Rollback transaction on error
        $DBcon->rollBack();
        error_log("Excel import error: " . $e->getMessage());
        
        return [
            'success' => false,
            'message' => 'Error processing Excel file: ' . $e->getMessage()
        ];
    }
}

function parseXLSXFile($filename) {
    $students = [];
    
    // Check if ZIP extension is available
    if (!class_exists('ZipArchive')) {
        throw new Exception("ZIP extension is required for XLSX file processing. Please enable php_zip extension in your PHP configuration.");
    }
    
    $temp_dir = sys_get_temp_dir() . '/xlsx_extract_' . uniqid();
    if (!mkdir($temp_dir, 0755, true)) {
        throw new Exception("Could not create temporary directory for XLSX extraction.");
    }
    
    $zip = new ZipArchive();
    if ($zip->open($filename) !== TRUE) {
        deleteDirectory($temp_dir);
        throw new Exception("Could not open XLSX file. File may be corrupted or not a valid XLSX file.");
    }
    
    try {
        // Extract to temporary directory
        $zip->extractTo($temp_dir);
        $zip->close();
        
        // Parse shared strings
        $sharedStrings = parseSharedStrings($temp_dir);
        
        // Parse worksheet data
        $students = parseWorksheet($temp_dir, $sharedStrings);
        
    } catch (Exception $e) {
        deleteDirectory($temp_dir);
        throw $e;
    }
    
    deleteDirectory($temp_dir);
    return $students;
}

function parseSharedStrings($temp_dir) {
    $sharedStrings = [];
    $sharedStringsFile = $temp_dir . '/xl/sharedStrings.xml';
    
    if (!file_exists($sharedStringsFile)) {
        return $sharedStrings;
    }
    
    $content = file_get_contents($sharedStringsFile);
    
    // Extract all text elements from shared strings
    preg_match_all('/<t[^>]*>(.*?)<\/t>/s', $content, $matches);
    
    foreach ($matches[1] as $index => $text) {
        $sharedStrings[$index] = html_entity_decode($text, ENT_QUOTES | ENT_XML1, 'UTF-8');
    }
    
    return $sharedStrings;
}

function parseWorksheet($temp_dir, $sharedStrings) {
    $students = [];
    $sheetFile = $temp_dir . '/xl/worksheets/sheet1.xml';
    
    if (!file_exists($sheetFile)) {
        throw new Exception("Could not find worksheet data in the XLSX file.");
    }
    
    $content = file_get_contents($sheetFile);
    
    // Extract all rows
    preg_match_all('/<row[^>]*>(.*?)<\/row>/s', $content, $rowMatches);
    
    foreach ($rowMatches[1] as $rowIndex => $rowContent) {
        if ($rowIndex == 0) continue; // Skip header row
        
        $rowData = parseRowData($rowContent, $sharedStrings);
        
        // Excel file structure:
        // Column A: Full Name
        // Column B: Registration Number
        if (count($rowData) >= 2) {
            $student = [
                'fullname' => $rowData['A'] ?? '',
                'regno' => $rowData['B'] ?? ''
            ];
            
            // Only add if we have both name and regno
            if (!empty(trim($student['fullname'])) && !empty(trim($student['regno']))) {
                $students[] = $student;
            }
        }
    }
    
    return $students;
}

function parseRowData($rowContent, $sharedStrings) {
    $rowData = [];
    
    // Extract all cells in the row with their column references
    preg_match_all('/<c[^>]*r="([A-Z])(\d+)"[^>]*>(.*?)<\/c>/s', $rowContent, $cellMatches, PREG_SET_ORDER);
    
    foreach ($cellMatches as $cellMatch) {
        $column = $cellMatch[1]; // Column letter (A, B, C, etc.)
        $cellContent = $cellMatch[3];
        
        // Extract cell value
        preg_match('/<v[^>]*>(.*?)<\/v>/s', $cellContent, $valueMatch);
        if (!isset($valueMatch[1])) {
            $rowData[$column] = '';
            continue;
        }
        
        $cellValue = trim($valueMatch[1]);
        
        // Check if cell uses shared string
        if (strpos($cellMatch[0], 't="s"') !== false) {
            // Value is an index into shared strings
            $stringIndex = intval($cellValue);
            $rowData[$column] = $sharedStrings[$stringIndex] ?? '';
        } else {
            // Direct value
            $rowData[$column] = $cellValue;
        }
    }
    
    return $rowData;
}

function deleteDirectory($dir) {
    if (!file_exists($dir)) return true;
    if (!is_dir($dir)) return unlink($dir);
    
    foreach (scandir($dir) as $item) {
        if ($item == '.' || $item == '..') continue;
        if (!deleteDirectory($dir . DIRECTORY_SEPARATOR . $item)) {
            return false;
        }
    }
    
    return rmdir($dir);
}

function registrationNumberExists($regno) {
    global $DBcon;
    try {
        $sql = "SELECT COUNT(*) FROM students_info WHERE regno = :regno";
        $stmt = $DBcon->prepare($sql);
        $stmt->execute([':regno' => $regno]);
        return $stmt->fetchColumn() > 0;
    } catch (PDOException $e) {
        error_log("Check regno error: " . $e->getMessage());
        return true; // Return true to prevent insertion on error
    }
}

function insertStudent($fullname, $regno, $class_id, $admin_year) {
    global $DBcon;
    try {
        $sql = "INSERT INTO students_info (fullname, regno, class_id, admin_year) 
                VALUES (:fullname, :regno, :class_id, :admin_year)";
        $stmt = $DBcon->prepare($sql);
        return $stmt->execute([
            ':fullname' => $fullname,
            ':regno' => $regno,
            ':class_id' => $class_id,
            ':admin_year' => $admin_year
        ]);
    } catch (PDOException $e) {
        error_log("Insert student error: " . $e->getMessage());
        return false;
    }
}

// Generate CSRF token
$csrf_token = generate_csrf_token();
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <!-- Required meta tags -->
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <title>Import Students - School Admin</title>
  <!-- plugins:css -->
  <link rel="stylesheet" href="vendors/iconfonts/font-awesome/css/all.min.css">
  <link rel="stylesheet" href="vendors/css/vendor.bundle.base.css">
  <link rel="stylesheet" href="vendors/css/vendor.bundle.addons.css">
  <!-- endinject -->
  <!-- plugin css for this page -->
  <!-- End plugin css for this page -->
  <!-- inject:css -->
  <link rel="stylesheet" href="css/style.css">
  <!-- endinject -->
  <style>
    .alert {
        padding: 12px 20px;
        border-radius: 4px;
        margin-bottom: 20px;
    }
    .alert-success {
        background-color: #d4edda;
        color: #155724;
        border: 1px solid #c3e6cb;
    }
    .alert-error {
        background-color: #f8d7da;
        color: #721c24;
        border: 1px solid #f5c6cb;
    }
    .alert-warning {
        background-color: #fff3cd;
        color: #856404;
        border: 1px solid #ffeaa7;
    }
    .form-group {
        margin-bottom: 1.5rem;
    }
    .card-title {
        display: flex;
        align-items: center;
    }
    .card-title i {
        margin-right: 10px;
    }
    .import-stats {
        background: #f8f9fa;
        border-radius: 4px;
        padding: 15px;
        margin-top: 20px;
    }
    .error-details {
        max-height: 200px;
        overflow-y: auto;
        background: white;
        padding: 10px;
        border-radius: 4px;
        margin-top: 10px;
        font-size: 0.9em;
    }
    .file-upload-area {
        border: 2px dashed #dee2e6;
        border-radius: 4px;
        padding: 40px 20px;
        text-align: center;
        background: #f8f9fa;
        transition: all 0.3s ease;
        cursor: pointer;
    }
    .file-upload-area:hover {
        border-color: #007bff;
        background: #e9ecef;
    }
    .file-upload-area.dragover {
        border-color: #007bff;
        background: #e3f2fd;
    }
    .file-input {
        display: none;
    }
    .file-info {
        margin-top: 10px;
        font-size: 0.9em;
        color: #6c757d;
    }
    .instructions-box {
        background: #e8f4fd;
        border-left: 4px solid #007bff;
        padding: 15px;
        margin-bottom: 20px;
        border-radius: 4px;
    }
  </style>
</head>
<body>
  <div class="container-scroller">
  <?php include("header.php"); ?>
    <div class="container-fluid page-body-wrapper">
      <!-- partial:partials/_settings-panel.html -->
      <?php include("sidebar.php"); ?>
      <div class="main-panel">
        <div class="content-wrapper">
          <div class="page-header">
            <h3 class="page-title">
              Import Students
            </h3>
            <nav aria-label="breadcrumb">
              <ol class="breadcrumb">
                <li class="breadcrumb-item"><a href="#">Dashboard</a></li>
                <li class="breadcrumb-item"><a href="students_crud.php">Students</a></li>
                <li class="breadcrumb-item active" aria-current="page">Import</li>
              </ol>
            </nav>
          </div>
          
          <!-- Display Messages -->
          <?php if ($message): ?>
          <div class="alert alert-<?php echo $message_type === 'success' ? 'success' : 'error'; ?>">
            <?php echo $message; ?>
          </div>
          <?php endif; ?>

          <?php if (!class_exists('ZipArchive')): ?>
          <div class="alert alert-warning">
            <strong>Warning:</strong> ZIP extension is not enabled on your server. 
            Please contact your hosting provider to enable the <code>php_zip</code> extension.
          </div>
          <?php endif; ?>
          
          <div class="row">
            <div class="col-md-8 grid-margin stretch-card">
              <div class="card">
                <div class="card-body">
                  <h4 class="card-title">
                    <i class="fas fa-file-import"></i>
                    Import Students from Excel
                  </h4>

                  <div class="instructions-box">
                    <h6><i class="fas fa-info-circle"></i> Important Instructions:</h6>
                    <ul class="mb-0">
                      <li>Excel file must be in .xlsx format</li>
                      <li>First row should contain column headers</li>
                      <li>Column A: Student Full Name</li>
                      <li>Column B: Registration Number</li>
                      <li>Maximum file size: 10MB</li>
                    </ul>
                  </div>
                  
                  <form method="POST" enctype="multipart/form-data" id="importForm">
                    <input type="hidden" name="csrf_token" value="<?php echo $csrf_token; ?>">
                    
                    <div class="row">
                      <div class="col-md-6">
                        <div class="form-group">
                          <label for="class_id">Class *</label>
                          <select class="form-control" id="class_id" name="class_id" required>
                            <option value="">Select Class</option>
                            <?php foreach ($classes as $class): ?>
                            <option value="<?php echo htmlspecialchars($class['classid']); ?>">
                              <?php echo htmlspecialchars($class['classid']); ?>
                            </option>
                            <?php endforeach; ?>
                          </select>
                        </div>
                      </div>
                      <div class="col-md-6">
                        <div class="form-group">
                          <label for="admin_year">Academic Year *</label>
                          <select class="form-control" id="admin_year" name="admin_year" required>
                            <option value="">Select Academic Year</option>
                            <?php foreach ($academic_years as $year): ?>
                            <option value="<?php echo htmlspecialchars($year['academic_year']); ?>">
                              <?php echo htmlspecialchars($year['academic_year']); ?>
                            </option>
                            <?php endforeach; ?>
                          </select>
                        </div>
                      </div>
                    </div>
                    
                    <div class="form-group">
                      <label>Excel File Upload *</label>
                      <div class="file-upload-area" id="fileUploadArea">
                        <i class="fas fa-cloud-upload-alt fa-3x text-muted mb-3"></i>
                        <h5>Drag & Drop your Excel file here</h5>
                        <p class="text-muted">or click to browse</p>
                        <input type="file" class="file-input" id="excel_file" name="excel_file" accept=".xlsx" required>
                        <div class="file-info" id="fileInfo">No file selected</div>
                      </div>
                    </div>
                    
                    <div class="form-group">
                      <div class="form-check">
                        <label class="form-check-label">
                          <input type="checkbox" class="form-check-input" name="confirm_import" required>
                          I confirm that the Excel file follows the required format
                        </label>
                      </div>
                    </div>
                    
                    <div class="form-group">
                      <button type="submit" name="import" class="btn btn-primary mr-2" <?php echo !class_exists('ZipArchive') ? 'disabled' : ''; ?>>
                        <i class="fas fa-upload"></i> Import Students
                      </button>
                      <a href="students_crud.php" class="btn btn-light">Back to Students</a>
                    </div>
                  </form>
                  
                  <?php if ($import_stats['errors'] > 0 && !empty($import_stats['error_details'])): ?>
                  <div class="import-stats">
                    <h6>Import Error Details:</h6>
                    <div class="error-details">
                      <?php foreach ($import_stats['error_details'] as $error): ?>
                      <div class="text-danger"><?php echo htmlspecialchars($error); ?></div>
                      <?php endforeach; ?>
                    </div>
                  </div>
                  <?php endif; ?>
                </div>
              </div>
            </div>
            
            <div class="col-md-4 grid-margin stretch-card">
              <div class="card">
                <div class="card-body">
                  <h4 class="card-title">
                    <i class="fas fa-table"></i>
                    Excel Format
                  </h4>
                  
                  <h6>Required Columns:</h6>
                  <div class="table-responsive">
                    <table class="table table-bordered table-sm">
                      <thead class="thead-light">
                        <tr>
                          <th>Column</th>
                          <th>Content</th>
                          <th>Required</th>
                        </tr>
                      </thead>
                      <tbody>
                        <tr>
                          <td>A</td>
                          <td>Full Name</td>
                          <td><span class="text-success">Yes</span></td>
                        </tr>
                        <tr>
                          <td>B</td>
                          <td>Registration Number</td>
                          <td><span class="text-success">Yes</span></td>
                        </tr>
                      </tbody>
                    </table>
                  </div>
                  
                  <h6 class="mt-4">Sample Data:</h6>
                  <div class="table-responsive">
                    <table class="table table-bordered table-sm">
                      <thead class="thead-light">
                        <tr>
                          <th>A</th>
                          <th>B</th>
                        </tr>
                      </thead>
                      <tbody>
                        <tr>
                          <td>John Doe</td>
                          <td>REG001</td>
                        </tr>
                        <tr>
                          <td>Jane Smith</td>
                          <td>REG002</td>
                        </tr>
                        <tr>
                          <td>...</td>
                          <td>...</td>
                        </tr>
                      </tbody>
                    </table>
                  </div>
                  
                  <div class="mt-3">
                    <button type="button" class="btn btn-outline-primary btn-sm" id="downloadTemplate">
                      <i class="fas fa-download"></i> Download Template
                    </button>
                  </div>
                </div>
              </div>
            </div>
          </div>
        </div>
        <!-- partial:partials/_footer.html -->
        <footer class="footer">
          <div class="d-sm-flex justify-content-center justify-content-sm-between">
            <span class="text-muted text-center text-sm-left d-block d-sm-inline-block">Copyright © 2018. All rights reserved.</span>
            <span class="float-none float-sm-right d-block mt-1 mt-sm-0 text-center">Hand-crafted & made with <i class="far fa-heart text-danger"></i></span>
          </div>
        </footer>
        <!-- partial -->
      </div>
    </div>
  </div>
  
  <!-- plugins:js -->
  <script src="vendors/js/vendor.bundle.base.js"></script>
  <script src="vendors/js/vendor.bundle.addons.js"></script>
  <!-- endinject -->
  <!-- Plugin js for this page-->
  <!-- End plugin js for this page-->
  <!-- inject:js -->
  <script src="js/off-canvas.js"></script>
  <script src="js/hoverable-collapse.js"></script>
  <script src="js/misc.js"></script>
  <script src="js/settings.js"></script>
  <script src="js/todolist.js"></script>
  <!-- endinject -->
  <!-- Custom js for this page-->
  <script>
    document.addEventListener('DOMContentLoaded', function() {
      const fileUploadArea = document.getElementById('fileUploadArea');
      const fileInput = document.getElementById('excel_file');
      const fileInfo = document.getElementById('fileInfo');
      const downloadTemplate = document.getElementById('downloadTemplate');
      
      // File upload area click handler
      fileUploadArea.addEventListener('click', function() {
        fileInput.click();
      });
      
      // File input change handler
      fileInput.addEventListener('change', function(e) {
        if (this.files.length > 0) {
          const file = this.files[0];
          fileInfo.innerHTML = `
            <strong>Selected file:</strong> ${file.name}<br>
            <small>Size: ${(file.size / 1024 / 1024).toFixed(2)} MB</small>
          `;
        } else {
          fileInfo.textContent = 'No file selected';
        }
      });
      
      // Drag and drop handlers
      fileUploadArea.addEventListener('dragover', function(e) {
        e.preventDefault();
        this.classList.add('dragover');
      });
      
      fileUploadArea.addEventListener('dragleave', function(e) {
        e.preventDefault();
        this.classList.remove('dragover');
      });
      
      fileUploadArea.addEventListener('drop', function(e) {
        e.preventDefault();
        this.classList.remove('dragover');
        
        if (e.dataTransfer.files.length > 0) {
          fileInput.files = e.dataTransfer.files;
          const file = e.dataTransfer.files[0];
          fileInfo.innerHTML = `
            <strong>Selected file:</strong> ${file.name}<br>
            <small>Size: ${(file.size / 1024 / 1024).toFixed(2)} MB</small>
          `;
        }
      });
      
      // Template download handler
      downloadTemplate.addEventListener('click', function(e) {
        e.preventDefault();
        
        // Create a simple CSV template
        const templateData = [
          ['Full Name', 'Registration Number'],
          ['John Doe', 'REG001'],
          ['Jane Smith', 'REG002'],
          ['Michael Johnson', 'REG003']
        ];
        
        let csvContent = "data:text/csv;charset=utf-8,";
        templateData.forEach(row => {
          csvContent += row.join(",") + "\r\n";
        });
        
        const encodedUri = encodeURI(csvContent);
        const link = document.createElement("a");
        link.setAttribute("href", encodedUri);
        link.setAttribute("download", "student_import_template.csv");
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
      });
      
      // Form validation
      document.getElementById('importForm').addEventListener('submit', function(e) {
        const classId = document.getElementById('class_id').value;
        const adminYear = document.getElementById('admin_year').value;
        const file = document.getElementById('excel_file').files[0];
        const confirmCheck = document.querySelector('input[name="confirm_import"]');
        
        if (!classId || !adminYear || !file || !confirmCheck.checked) {
          e.preventDefault();
          alert('Please fill all required fields and confirm the import.');
        }
      });

      // Auto-hide alerts after 5 seconds
      setTimeout(function() {
        const alerts = document.querySelectorAll('.alert');
        alerts.forEach(alert => {
          alert.style.opacity = '0';
          alert.style.transition = 'opacity 0.5s ease';
          setTimeout(() => alert.remove(), 500);
        });
      }, 5000);
    });
  </script>
</body>
</html>